import openpyxl
import pymysql

# 写入数据到 Excel 文件
def write_to_excel(file_path):
    # 创建一个新的工作簿
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    headers = ['id', 'idcard', 'username', 'realname', 'pwd', 'telphone', 'email', 'age', 'sex', 'address', 'hiredate', 'sal', 'job', 'company']
    sheet.append(headers)
    # 写入 2000 行的数据
    for row in range(1, 2001):
        data = [
            row,
            f'IDCard{row}',
            f'Username{row}',
            f'Realname{row}',
            f'Password{row}',
            f'1234567890{row % 10}',
            f'user{row}@example.com',
            20 + row % 30,
            'Male' if row % 2 == 0 else 'Female',
            f'Address{row}',
            '2023-01-01',
            5000 + row * 100,
            f'Job{row % 10}',
            f'Company{row % 5}'
        ]
        sheet.append(data)
    # 保存工作簿
    workbook.save(file_path)

# 从 Excel 文件读取数据
def read_from_excel(file_path):
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook.active
    data = []
    for row in sheet.iter_rows(min_row=2, values_only=True):
        data.append(row)
    return data

# 将数据写入 MySQL 数据库
def write_to_mysql(data):
    connection = None  # 初始化 connection 为 None
    try:
        # 连接到 MySQL 数据库
        connection = pymysql.connect(
            host='localhost',
            user='root',
            password='root',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        with connection.cursor() as cursor:
            create_database_query = "CREATE DATABASE IF NOT EXISTS my_database"
            cursor.execute(create_database_query)
            use_database_query = "USE my_database"
            cursor.execute(use_database_query)
            # 创建一个表（如果不存在）
            create_table_query = """
            CREATE TABLE IF NOT EXISTS user_info (
                id int(11) NOT NULL AUTO_INCREMENT,
                idcard varchar(50) NOT NULL,
                username varchar(50) NOT NULL,
                realname varchar(50) NOT NULL,
                pwd varchar(50) NOT NULL,
                telphone varchar(12) NOT NULL,
                email varchar(100) NOT NULL,
                age int(11) NOT NULL,
                sex varchar(20) NOT NULL,
                address varchar(200) NOT NULL,
                hiredate date NOT NULL,
                sal DECIMAL(9, 2) NOT NULL,
                job varchar(100) NOT NULL,
                company varchar(100) NOT NULL,
                PRIMARY KEY (id)
            )
            """
            cursor.execute(create_table_query)
            # 插入数据
            insert_query = """
            INSERT INTO user_info (id, idcard, username, realname, pwd, telphone, email, age, sex, address, hiredate, sal, job, company)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            for row in data:
                cursor.execute(insert_query, row)
        # 提交更改
        connection.commit()
    except pymysql.Error as e:
        print(f"数据库错误: {e}")
    finally:
        if connection:
            connection.close()

if __name__ == "__main__":
    file_path = "example.xlsx"
    # 写入数据到 Excel
    write_to_excel(file_path)
    # 从 Excel 读取数据
    data = read_from_excel(file_path)
    # 将数据写入 MySQL
    write_to_mysql(data)